package com.moduls.interfaceFunction.core;

import org.apache.ibatis.builder.StaticSqlSource;
import org.apache.ibatis.exceptions.TooManyResultsException;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

/**
 * MyBatis执行sql工具，在写SQL的时候建议使用参数形式的可以是${}或#{}
 *
 * 不建议将参数直接拼到字符串中，当大量这么使用的时候由于缓存MappedStatement而占用更多的内存
 *
 * @author liuzh
 * @since 2015-03-10
 */
public class SqlMapper {
    private final MSUtils msUtils;
    private final SqlSession sqlSession;
    public int pagesize = 12;

    /**
     * 构造方法，默认缓存MappedStatement
     *
     * @param sqlSession
     */
    public SqlMapper(SqlSession sqlSession) {
        this.sqlSession = sqlSession;
        this.msUtils = new MSUtils(sqlSession.getConfiguration());
    }
    /**
	 * 获取当前时间
	 */
	public String public_getcurrenttime() {
		Date dt = new Date();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date_now = sdf.format(dt);
		return date_now;
	}
	
	/**
	 * 查询全部数据，针对不需要分页的查询
	 */
	public List<Map<String, Object>> public_list_all_sql(String sql)  {

		List<Map<String, Object>> Result = selectList(sql, null);

		return Result;
	}
	
	/**
	 * 分页查询多条记录（最外层的select、from、where大写，内层的关键字都小写）
	 */
	public PageModel public_list_pagenum_sql(String sql, int pagenum) {

		PageModel pagemodel = new PageModel();
		pagemodel.current_page = pagenum;
		try
		{
			String spagesql = "SELECT count(*) as cnt";
			spagesql = spagesql + sql.substring(sql.indexOf(" FROM"));
			String cnt = this.public_one_scale(spagesql);
			pagemodel.total_rows = Integer.parseInt(cnt);
			if(pagemodel.total_rows % this.pagesize == 0)
			{
				pagemodel.total_pages = pagemodel.total_rows % this.pagesize;
			}
			else
			{
				pagemodel.total_pages = pagemodel.total_rows / this.pagesize + 1;
			}			
		}
		catch(Exception e)
		{
			
		}
		
		pagemodel.mapRlt =  public_list_page_sql(sql, pagenum, 0);
		
		return pagemodel;
	}
	
	
	/**
	 * 分页查询多条记录
	 */
	public List<Map<String, Object>> public_list_page_sql(String sql, int pagenum) {

		return public_list_page_sql(sql, pagenum, 0);
	}
	public List<Map<String, Object>> public_list_page_sql(String sql, int pagenum, int pagesize)  {
		if (pagenum == 0) {
			pagenum = 1;
		}
		if (pagesize == 0) {
			pagesize = this.pagesize;
		}
		int limitStart = (pagenum - 1) * pagesize;

		sql += " LIMIT " + limitStart + "," + pagesize;
		List<Map<String, Object>> Result = selectList(sql, null);

		return Result;
	}
	
	/** 单表特定列一条数据 */
	public Map<String, Object> public_one_detail(String sql) {
		Map<String, Object> Result = selectOne(sql, null);

		return Result;
	}

	private String implodeFieldValue_forwhere(Map<String, String> data) {
		String sql = "", comma = "";
		for (String k : data.keySet()) {
			sql += comma + "`" + k + "`='" + data.get(k).toString() + "'";
			comma = " and ";

		}
		return sql;

	}

	/** 获取单表第一行第一列 */
	public String public_one_scale(String sql) throws SQLException {
		Map<String, Object> Result = selectOne(sql, null);
		if (Result== null || Result.isEmpty()) {
			return null;
		} else {
	    	String obj = null;
	        for (Entry<String, Object> entry : Result.entrySet()) {
	            obj = entry.getValue().toString();
	            if (obj != null) {
	                break;
	            }
	        }
	        return  obj;
		}

	}
	
	
    /**
     * 获取List中最多只有一个的数据
     *
     * @param list List结果
     * @param <T>  泛型类型
     * @return
     */
    private <T> T getOne(List<T> list) {
        if (list.size() == 1) {
            return list.get(0);
        } else if (list.size() > 1) {
            throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + list.size());
        } else {
            return null;
        }
    }

    /**
     * 查询返回一个结果，多个结果时抛出异常
     *
     * @param sql 执行的sql
     * @return
     */
    public Map<String, Object> selectOne(String sql) {
        List<Map<String, Object>> list = selectList(sql);
        return getOne(list);
    }

    /**
     * 查询返回一个结果，多个结果时抛出异常
     *
     * @param sql   执行的sql
     * @param value 参数
     * @return
     */
    private Map<String, Object> selectOne(String sql, Object value) {
        List<Map<String, Object>> list = selectList(sql, value);
        return getOne(list);
    }

    /**
     * 查询返回List<Map<String, Object>>
     *
     * @param sql 执行的sql
     * @return
     */
    private List<Map<String, Object>> selectList(String sql) {
        String msId = msUtils.select(sql);
        List<Map<String, Object>> lstmap = sqlSession.selectList(msId);
        return this.clearNullListVaue(lstmap);
    }
    
    /**
     * 遍历清空null值
     *
     * @return
     */
    private List<Map<String, Object>> clearNullListVaue(List<Map<String, Object>> lstmap)
    {
    	for(Map<String, Object> map : lstmap)
    	{
    		this.clearNullMapVaue(map);
    	}
    	return lstmap;
    }
    
    /**
     * 遍历清空null值
     *
     * @return
     */
    private Map<String, Object> clearNullMapVaue(Map<String, Object> map)
    {
    	for(Map.Entry<String, Object> entry : map.entrySet())
    	{
    		if(entry.getValue() == null)
    		{
    			entry.setValue("");
    		}
    	}
    	return map;
    }

    /**
     * 查询返回List<Map<String, Object>>
     *
     * @param sql   执行的sql
     * @param value 参数
     * @return
     */
    private List<Map<String, Object>> selectList(String sql, Object value) {
        Class<?> parameterType = value != null ? value.getClass() : null;
        String msId = msUtils.selectDynamic(sql, parameterType);
        List<Map<String, Object>> lstmap= sqlSession.selectList(msId, value);
        return this.clearNullListVaue(lstmap);
    }

  
    /** 新增和修改 where不为空就表示修改 */
	public int insert_or_update(String tablename, Map<String, String> data, String where) throws SQLException {
		int res = -1;
		String sql = implodeFieldValue_forinsert(data);

		if (where != null && where != "") {// 更新
			res = update("UPDATE " + tablename + " SET " + sql + " where " + where, null);
		} else {// 新增
			res = insert("INSERT INTO " + tablename + " SET " + sql, null);
			res=Integer.parseInt(selectOne("SELECT LAST_INSERT_ID() AS ID").get("ID").toString());
		}

		return res;
	}
	
	private String implodeFieldValue_forinsert(Map<String, String> data) {
		String sql = "", comma = "";
		for (String k : data.keySet()) {
			if (data.get(k).toString().equals("")) {
				sql += comma + "`" + k + "`=null";
			} else {
				sql += comma + "`" + k + "`='" + data.get(k).toString() + "'";
			}

			comma = ",";

		}
		return sql;
	}

    /**
     * 插入数据
     *
     * @param sql 执行的sql
     * @return
     */
    public int insert(String sql) {
        String msId = msUtils.insert(sql);
        return sqlSession.insert(msId);
    }

    /**
     * 插入数据
     *
     * @param sql   执行的sql
     * @param value 参数
     * @return
     */
    public int insert(String sql, Object value) {
        Class<?> parameterType = value != null ? value.getClass() : null;
        String msId = msUtils.insertDynamic(sql, parameterType);
        return sqlSession.insert(msId, value);
    }

    /**
     * 更新数据
     *
     * @param sql 执行的sql
     * @return
     */
    public int update(String sql) {
        String msId = msUtils.update(sql);
        return sqlSession.update(msId);
    }

    /**
     * 更新数据
     *
     * @param sql   执行的sql
     * @param value 参数
     * @return
     */
    public int update(String sql, Object value) {
        Class<?> parameterType = value != null ? value.getClass() : null;
        String msId = msUtils.updateDynamic(sql, parameterType);
        return sqlSession.update(msId, value);
    }

    /**
     * 删除数据
     *
     * @param sql 执行的sql
     * @return
     */
    public int delete(String sql) {
        String msId = msUtils.delete(sql);
        return sqlSession.delete(msId);
    }

    /**
     * 删除数据
     *
     * @param sql   执行的sql
     * @param value 参数
     * @return
     */
    public int delete(String sql, Object value) {
        Class<?> parameterType = value != null ? value.getClass() : null;
        String msId = msUtils.deleteDynamic(sql, parameterType);
        return sqlSession.delete(msId, value);
    }

    private class MSUtils {
        private Configuration configuration;
        private LanguageDriver languageDriver;

        private MSUtils(Configuration configuration) {
            this.configuration = configuration;
            languageDriver = configuration.getDefaultScriptingLanuageInstance();
        }

        /**
         * 创建MSID
         *
         * @param sql 执行的sql
         * @param sql 执行的sqlCommandType
         * @return
         */
        private String newMsId(String sql, SqlCommandType sqlCommandType) {
            StringBuilder msIdBuilder = new StringBuilder(sqlCommandType.toString());
            msIdBuilder.append(".").append(sql.hashCode());
            return msIdBuilder.toString();
        }

        /**
         * 是否已经存在该ID
         *
         * @param msId
         * @return
         */
        private boolean hasMappedStatement(String msId) {
            return configuration.hasStatement(msId, false);
        }

        /**
         * 创建一个查询的MS
         *
         * @param msId
         * @param sqlSource  执行的sqlSource
         * @param resultType 返回的结果类型
         */
        private void newSelectMappedStatement(String msId, SqlSource sqlSource, final Class<?> resultType) {
            MappedStatement ms = new MappedStatement.Builder(configuration, msId, sqlSource, SqlCommandType.SELECT)
                    .resultMaps(new ArrayList<ResultMap>() {
                        {
                            add(new ResultMap.Builder(configuration, "defaultResultMap", resultType, new ArrayList<ResultMapping>(0)).build());
                        }
                    })
                    .build();
            //缓存
            configuration.addMappedStatement(ms);
        }

        /**
         * 创建一个简单的MS
         *
         * @param msId
         * @param sqlSource      执行的sqlSource
         * @param sqlCommandType 执行的sqlCommandType
         */
        private void newUpdateMappedStatement(String msId, SqlSource sqlSource, SqlCommandType sqlCommandType) {
            MappedStatement ms = new MappedStatement.Builder(configuration, msId, sqlSource, sqlCommandType)
                    .resultMaps(new ArrayList<ResultMap>() {
                        {
                            add(new ResultMap.Builder(configuration, "defaultResultMap", int.class, new ArrayList<ResultMapping>(0)).build());
                        }
                    })
                    .build();
            //缓存
            configuration.addMappedStatement(ms);
        }

        private String select(String sql) {
            String msId = newMsId(sql, SqlCommandType.SELECT);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            StaticSqlSource sqlSource = new StaticSqlSource(configuration, sql);
            newSelectMappedStatement(msId, sqlSource, Map.class);
            return msId;
        }

        private String selectDynamic(String sql, Class<?> parameterType) {
            String msId = newMsId(sql + parameterType, SqlCommandType.SELECT);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);
            newSelectMappedStatement(msId, sqlSource, Map.class);
            return msId;
        }

        private String select(String sql, Class<?> resultType) {
            String msId = newMsId(resultType + sql, SqlCommandType.SELECT);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            StaticSqlSource sqlSource = new StaticSqlSource(configuration, sql);
            newSelectMappedStatement(msId, sqlSource, resultType);
            return msId;
        }

        private String selectDynamic(String sql, Class<?> parameterType, Class<?> resultType) {
            String msId = newMsId(resultType + sql + parameterType, SqlCommandType.SELECT);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);
            newSelectMappedStatement(msId, sqlSource, resultType);
            return msId;
        }

        private String insert(String sql) {
            String msId = newMsId(sql, SqlCommandType.INSERT);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            StaticSqlSource sqlSource = new StaticSqlSource(configuration, sql);
            newUpdateMappedStatement(msId, sqlSource, SqlCommandType.INSERT);
            return msId;
        }

        private String insertDynamic(String sql, Class<?> parameterType) {
            String msId = newMsId(sql + parameterType, SqlCommandType.INSERT);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);
            newUpdateMappedStatement(msId, sqlSource, SqlCommandType.INSERT);
            return msId;
        }

        private String update(String sql) {
            String msId = newMsId(sql, SqlCommandType.UPDATE);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            StaticSqlSource sqlSource = new StaticSqlSource(configuration, sql);
            newUpdateMappedStatement(msId, sqlSource, SqlCommandType.UPDATE);
            return msId;
        }

        private String updateDynamic(String sql, Class<?> parameterType) {
            String msId = newMsId(sql + parameterType, SqlCommandType.UPDATE);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);
            newUpdateMappedStatement(msId, sqlSource, SqlCommandType.UPDATE);
            return msId;
        }

        private String delete(String sql) {
            String msId = newMsId(sql, SqlCommandType.DELETE);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            StaticSqlSource sqlSource = new StaticSqlSource(configuration, sql);
            newUpdateMappedStatement(msId, sqlSource, SqlCommandType.DELETE);
            return msId;
        }

        private String deleteDynamic(String sql, Class<?> parameterType) {
            String msId = newMsId(sql + parameterType, SqlCommandType.DELETE);
            if (hasMappedStatement(msId)) {
                return msId;
            }
            SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, parameterType);
            newUpdateMappedStatement(msId, sqlSource, SqlCommandType.DELETE);
            return msId;
        }
    }
}
